package com.work.service.impl;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.pagehelper.PageHelper;
import com.work.constant.ExcelConstants;
import com.work.domain.Person;
import com.work.mapper.PersonMapper;
import com.work.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/**
 * @author: YanL
 * @createTime: 2023-01-03  10:31
 * @version: 1.0.0
 * @description: excel导出Service实现类
 */
@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {

  /**
   * 字符集编码
   */
  private static final String CHARACTER_CODE = "UTF-8";

  /**
   * 个人信息Mapper组件
   */
  @Autowired
  private PersonMapper personMapper;


  @Override
  public void exportExcel(HttpServletResponse response) {
    OutputStream outputStream = null;
    try {
      outputStream = response.getOutputStream();

      // 开始时间
      long bTime = System.currentTimeMillis();
      log.info("开始导出时间:{}", bTime);

      ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
      String fileName = new String(("ExcelExport").getBytes(), CHARACTER_CODE);

      // 创建excel列名
      Table table = new Table(1);
      ArrayList<List<String>> titles = new ArrayList<>();
      titles.add(Collections.singletonList("id"));
      titles.add(Collections.singletonList("name"));
      titles.add(Collections.singletonList("sex"));
      titles.add(Collections.singletonList("age"));
      titles.add(Collections.singletonList("email"));
      titles.add(Collections.singletonList("tel"));
      titles.add(Collections.singletonList("address"));
      table.setHead(titles);

      // 模拟数据量
      int count = 4999999;
      // 记录总数:实际中需要根据查询条件进行统计查询
      int totalCount = personMapper.selectCount(new QueryWrapper<Person>());
      // 每一个Sheet存放100w条数据
      int sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
      // 每次写入的数据量20w
      int writeDataRows = ExcelConstants.PRE_WRITE_ROW_COUNT;
      // 计算需要的Sheet数量
      int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
      // 计算一般情况下每一个Sheet需要写入的次数（）
      int oneSheetWriteCount = sheetDataRows / writeDataRows;
      // 计算最后一个sheet需要写入的次数
      int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

      // 分批查询分次写入
      ArrayList<List<String>> dataList = new ArrayList<>();
      for (int i = 0; i < sheetNum; i++) {
        // 创建Sheet
        Sheet sheet = new Sheet(i, 0);
        // 循环写入次数：j的自增条件是 当最后一个Sheet写入的次数为正常的每个Sheet写入的次数，如果是最后一个就需要使用计算的次数LastSheetWriteCount
        for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
          // 集合复用 便于GC清理
          dataList.clear();
          // 分页查询一次20w
          PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
          List<Person> resultList = personMapper.findByPage();
          if (!CollectionUtils.isEmpty(resultList)) {
            resultList.forEach(item -> {
              dataList.add(Arrays.asList(item.getId().toString(), item.getName(), item.getSex(), item.getAge().toString(), item.getEmail(), item.getTel(), item.getAddress()));
            });
          }
          // 写入数据
          writer.write0(dataList, sheet, table);
        }
      }

      // 下载excel
      response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
      response.setContentType("multipart/form-data");
      response.setCharacterEncoding("utf-8");
      writer.finish();
      outputStream.flush();
      // 导出时间结束
      long eTime = System.currentTimeMillis();
      log.info("导出结束时间:" + eTime + "ms");
      log.info("导出所用时间:" + (eTime - bTime) / 1000 + "秒");
    } catch (Exception e) {
      log.error("excel导出异常:{}", e.getMessage());
    } finally {
      if (outputStream != null) {
        try {
          outputStream.close();
        } catch (Exception e) {
          log.error("流关闭异常:{}", e.getMessage());
        }
      }
    }
  }

}
